package org.javaweb.cms_web.mapper;

import org.apache.ibatis.annotations.*;
import org.javaweb.cms_web.model.Achievement;

import java.util.List;

@Mapper
public interface AchievementMapper {

    // 查询某个团队的所有科研成果（可选成果类别）
    @Select("<script>" +
            "SELECT * FROM achievements " +
            "WHERE team_id = #{teamID} AND visibility = 0 AND status = 1 " +
            "<if test='category != null'> AND category = #{category} </if>" +
            "ORDER BY creation_time DESC" +
            "</script>")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findAchievementsByTeam(@Param("teamID") int teamID, @Param("category") Integer category);

    // 根据团队ID和标题模糊查询科研成果（可选成果类别）
    @Select("<script>" +
            "SELECT * FROM achievements " +
            "WHERE team_id = #{teamID} AND visibility = 0 AND status = 1 " +
            "<if test='category != null'> AND category = #{category} </if>" +
            "AND title LIKE CONCAT('%', #{title}, '%') " +
            "ORDER BY creation_time DESC" +
            "</script>")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findAchievementsByTeamAndTitle(@Param("teamID") int teamID, @Param("title") String title, @Param("category") Integer category);

    // 插入新的科研成果记录
    @Insert("INSERT INTO Achievements (title, category, abstract, description, attachment_link, picture_link, creation_time, team_id, user_id, status, visibility) " +
            "VALUES (#{title}, #{category}, #{abstractText}, #{description}, #{attachmentLink}, #{pictureLink}, #{creationTime}, #{teamId}, #{userId}, #{status}, #{visibility})")
    int insert(Achievement achievement);

    // 查询所有科研成果
    @Select("SELECT * FROM Achievements")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findAll();

    // 根据ID删除科研成果
    @Delete("DELETE FROM Achievements WHERE achievement_id = #{achievementId}")
    int deleteById(Integer achievementId);

    // 更新成果的visibility
    @Update("UPDATE Achievements SET visibility = #{visibility} WHERE achievement_id = #{achievementId}")
    int updateVisibility(@Param("achievementId") Integer achievementId, @Param("visibility") int visibility);

    // 根据ID查询科研成果
    @Select("SELECT * FROM Achievements WHERE achievement_id = #{achievementId}")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    Achievement findById(@Param("achievementId") Integer achievementId);

    // 更新科研成果
    @Update("UPDATE Achievements SET title = #{title}, category = #{category}, abstract = #{abstractText}, description = #{description}, " +
            "attachment_link = #{attachmentLink}, picture_link = #{pictureLink}, creation_time = #{creationTime}, team_id = #{teamId}, " +
            "user_id = #{userId}, status = #{status}, visibility = #{visibility} WHERE achievement_id = #{achievementId}")
    int update(Achievement achievement);


    // 根据标题模糊搜索
    @Select("SELECT * FROM Achievements WHERE title LIKE CONCAT('%', #{title}, '%')")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findByTitleLike(String title);

    // 根据类别获取科研成果
    @Select("SELECT * FROM Achievements WHERE category = #{category}")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findByCategory(Integer category);

    // 根据日期范围查询科研成果
    @Select("SELECT * FROM Achievements WHERE creation_time BETWEEN #{startDate} AND #{endDate}")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);

    // 根据审核状态查询科研成果
    @Select("SELECT * FROM Achievements WHERE status = #{status}")
    @Results({
            @Result(property = "achievementId", column = "achievement_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "category", column = "category"),
            @Result(property = "abstractText", column = "abstract"),
            @Result(property = "description", column = "description"),
            @Result(property = "attachmentLink", column = "attachment_link"),
            @Result(property = "pictureLink", column = "picture_link"),
            @Result(property = "creationTime", column = "creation_time"),
            @Result(property = "teamId", column = "team_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "visibility", column = "visibility")
    })
    List<Achievement> findByStatus(Integer status);

    //更新拒绝理由
    @Update("UPDATE Achievements SET reject_reason = #{rejectReason} WHERE achievement_id = #{achievementId}")
    int updateRejectReason(@Param("achievementId") Integer achievementId, @Param("rejectReason") String rejectReason);

    @Update("UPDATE Achievements SET status = #{status} WHERE achievement_id = #{achievementId}")
    int updateStatus(@Param("achievementId") Integer achievementId, @Param("status") Integer status);
}

